﻿using BW.WMS.Application.Features.Supperss.Dtos;
using BW.WMS.Application.Features.Supperss.Interfice;
using BW.WMS.Core.Result;
using BW.WMS.Domain.Entities.Supperrs;
using BW.WMS.Infrastructure.Data.Repositories;
using BW.WMS.Infrastructure.Data.Contexts;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BW.WMS.Application.Features.Supperss.Services
{
    public class SupperProductServices : ISupperServices
    {
        private readonly DataRepository<ProductModel> _repository;
        private readonly DataRepository<SupplierModels> _supplierRepository;
        private readonly DBContext _dbContext;

        public SupperProductServices(DataRepository<ProductModel> repository, DataRepository<SupplierModels> supplierRepository, DBContext dbContext)
        {
            _repository = repository;
            _supplierRepository = supplierRepository;
            _dbContext = dbContext;
        }
        /// <summary>
        /// 添加商品
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiResult<int> AddProduct(ProductModel product)
        {
            var sql = @"
            INSERT INTO DB_product (
                name, specification, unit, category, status, create_time
            ) VALUES (
                @Name, @Specification, @Unit, @Category, @Status, @create_time
            );
            SELECT LAST_INSERT_ID()";
            _repository.Add(sql, product);

            return new ApiResult<int>() { Code = ApiEnums.Success, Message = "添加成功", Result = product.Id };
        }
        /// <summary>
        /// 添加供应商
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiResult<int> AddSupplier(SupplierModels supplier)
        {
            var sql = @"
            INSERT INTO DB_supplier (
                name, contact_person, contact_phone, address, 
                status, create_time
            ) VALUES (
                @Name, @contact_person, @contact_phone, @Address, 
                @Status, @create_time
            );
            SELECT LAST_INSERT_ID()";

            _supplierRepository.Add(sql, supplier);

            return new ApiResult<int>() { Code = ApiEnums.Success, Message = "添加成功", Result = supplier.Id };
        }
        /// <summary>
        /// 删除商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiResult<int> DeleteProduct(int id)
        {
            var sql = @"
                DELETE FROM DB_product 
                WHERE id = @Id;";
            var result = _dbContext.GetConn().Execute(sql, new { Id = id });
            
            return new ApiResult<int>() { Code = ApiEnums.Success, Message = "删除商品成功", Result = id };
        }
        /// <summary>
        /// 删除供应商
        /// </summary>
        public ApiResult<int> DeleteSupplier(int id)
        {
            var sql = @"
                    DELETE FROM DB_supplier 
                    WHERE id = @Id;";
            var result = _dbContext.GetConn().Execute(sql, new { Id = id });
            
            return new ApiResult<int>() { Code = ApiEnums.Success, Message = "删除供应商成功", Result = id };
        }
        /// <summary>
        /// 根据供应商id获取商品列表
        /// </summary>
        public ApiResult<List<ProductModel>> GetProductsBySupplierId(int supplierId)
        {
            var sql = @"
                SELECT 
                    p.id, 
                    p.name, 
                    p.specification, 
                    p.unit, 
                    p.category, 
                    p.status, 
                    p.create_time
                FROM DB_product p
                INNER JOIN DB_supplier_product sp ON p.id = sp.product_id
                WHERE sp.supplier_id = @SupplierId;";

            var products = _repository.GetList(sql, new { SupplierId = supplierId });

            if (products == null || !products.Any())
            {
                return new ApiResult<List<ProductModel>>() { Code = ApiEnums.Error, Message = "没有商品", Result = new List<ProductModel>() };
            }

            return new ApiResult<List<ProductModel>>() { Code = ApiEnums.Success, Message = "查询成功", Result = products };
        }
        /// <summary>
        /// 获取供应商列表
        /// </summary>
        /// <param name="queryDto"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiPage<SupplierModels> GetSupperProduct(SupperProductQueryDto queryDto)
        {
            var whereConditions = new List<string>();
            var parameters = new Dictionary<string, object>();

            if (!string.IsNullOrEmpty(queryDto.SName))
            {
                whereConditions.Add("name LIKE @Name");
                parameters["Name"] = $"%{queryDto.SName}%";
            }

            if (queryDto.Status != null)
            {
                whereConditions.Add("status = @Status");
                parameters["Status"] = queryDto.Status;
            }

            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";

            var dataSql = $@"
                SELECT 
                    id, 
                    name, 
                    contact_person, 
                    contact_phone, 
                    address, 
                    status, 
                    create_time
                FROM DB_supplier
                {whereClause}
                ORDER BY create_time DESC  
                LIMIT @PageSize OFFSET @Offset";

            var countSql = $@"
                SELECT COUNT(*) 
                FROM DB_supplier
                {whereClause}";

            // 添加分页参数
            parameters["PageSize"] = queryDto.PageSize;
            parameters["Offset"] = (queryDto.PageIndex - 1) * queryDto.PageSize;

            var suppliers = _supplierRepository.GetList(dataSql, parameters);
            var totalCount = _dbContext.GetConn().ExecuteScalar<int>(countSql, parameters);

            return new ApiPage<SupplierModels>()
            {
                TodalCount = totalCount,
                Message = "查询成功",
                PageData = suppliers
            };
        }
        /// <summary>
        /// 获取商品列表（去重）
        /// </summary>
        /// <param name="queryParams">查询参数</param>
        /// <returns>分页商品列表</returns>
        public ApiPage<ProductModel> GetSupperProductPage(ProducstDto queryParams)
        {
            // 参数验证
            if (queryParams == null)
                throw new ArgumentNullException(nameof(queryParams));

            if (queryParams.PageIndex < 1)
                queryParams.PageIndex = 1;

            if (queryParams.PageSize < 1 || queryParams.PageSize > 100)
                queryParams.PageSize = 10;

            // 计算分页偏移量
            var offset = (queryParams.PageIndex - 1) * queryParams.PageSize;

            // 构建查询条件
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 基础条件：只查询启用的商品
            conditions.Add("p.status = 1");

            if (!string.IsNullOrEmpty(queryParams.Name))
            {
                conditions.Add("p.name LIKE @Name");
                parameters.Add("@Name", $"%{queryParams.Name}%");
            }

            if (queryParams.SupplierId.HasValue)
            {
                conditions.Add("sp.supplier_id = @SupplierId");
                parameters.Add("@SupplierId", queryParams.SupplierId.Value);
            }

            var whereClause = "WHERE " + string.Join(" AND ", conditions);

            // 主查询：获取当前页数据（使用子查询避免重复）
            var selectSql = $@"
            SELECT p.*, 
                   COALESCE(GROUP_CONCAT(DISTINCT s.name SEPARATOR ', '), '') AS SupplierName
            FROM DB_product p
            LEFT JOIN DB_supplier_product sp ON p.id = sp.product_id
            LEFT JOIN DB_supplier s ON sp.supplier_id = s.id
            {whereClause}
            GROUP BY p.id, p.name, p.specification, p.unit, p.category, p.status, p.create_time
            ORDER BY p.create_time DESC
            LIMIT @PageSize OFFSET @Offset";

            // 总记录数查询（使用子查询确保准确性）
            var countSql = $@"
            SELECT COUNT(DISTINCT p.id) 
            FROM DB_product p
            LEFT JOIN DB_supplier_product sp ON p.id = sp.product_id
            LEFT JOIN DB_supplier s ON sp.supplier_id = s.id
            {whereClause}";

            // 添加分页参数
            parameters.Add("@PageSize", queryParams.PageSize);
            parameters.Add("@Offset", offset);

            try
            {
                // 执行查询获取数据
                var products = _repository.GetList(selectSql, parameters);

                // 执行查询获取总数
                var totalCount = _dbContext.GetConn().ExecuteScalar<int>(countSql, parameters);

                // 构建返回结果
                return new ApiPage<ProductModel>()
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    TodalCount = totalCount,
                    PageData = products ?? new List<ProductModel>()
                };
            }
            catch (Exception ex)
            {
                return new ApiPage<ProductModel>()
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    TodalCount = 0,
                    PageData = new List<ProductModel>()
                };
            }
        }


        /// <summary>
        /// 修改商品列表
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiResult<bool> UpdateProduct(ProductModel product)
        {
            try
            {
                // 先查询商品是否存在
                var checkSql = @"
                    SELECT id FROM DB_product 
                    WHERE id = @Id";

                var existingProduct = _repository.GetList(checkSql, new { Id = product.Id }).FirstOrDefault();

                if (existingProduct == null)
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Error,
                        Message = "没有此商品",
                        Result = false
                    };
                }

                // 更新商品信息
                var updateSql = @"
                    UPDATE DB_product SET 
                        name = @Name,
                        specification = @Specification,
                        unit = @Unit,
                        category = @Category,
                        status = @Status
                    WHERE id = @Id";

                var parameters = new
                {
                    product.Id,
                    product.Name,
                    product.Specification,
                    product.Unit,
                    product.Category,
                    product.Status
                };

                var result = _dbContext.GetConn().Execute(updateSql, parameters) > 0;

                if (result)
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Success,
                        Message = "修改成功",
                        Result = true
                    };
                }
                else
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Error,
                        Message = "修改失败",
                        Result = false
                    };
                }
            }
            catch (Exception ex)
            {
                return new ApiResult<bool>()
                {
                    Code = ApiEnums.Error,
                    Message = $"修改失败：{ex.Message}",
                    Result = false
                };
            }
        }
        /// <summary>
        /// 修改供应商列表
        /// </summary>
        /// <param name="supplier"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public ApiResult<int> UpdateSupplier(SupplierModels supplier)
        {
            try
            {
                // 先查询供应商是否存在
                var checkSql = @"
                    SELECT id FROM DB_supplier 
                    WHERE id = @Id";

                var existingSupplier = _supplierRepository.GetList(checkSql, new { Id = supplier.Id }).FirstOrDefault();

                if (existingSupplier == null)
                {
                    return new ApiResult<int>()
                    {
                        Code = ApiEnums.Error,
                        Message = "没有此供应商",
                        Result = 0
                    };
                }

                // 更新供应商信息
                var updateSql = @"
                    UPDATE DB_supplier SET 
                        name = @Name,
                        contact_person = @contact_person,
                        contact_phone = @contact_phone,
                        address = @Address,
                        status = @Status
                    WHERE id = @Id";

                var parameters = new
                {
                    supplier.Id,
                    supplier.Name,
                    supplier.contact_person,
                    supplier.contact_phone,
                    supplier.Address,
                    supplier.Status
                };

                var result = _dbContext.GetConn().Execute(updateSql, parameters);

                if (result > 0)
                {
                    return new ApiResult<int>()
                    {
                        Code = ApiEnums.Success,
                        Message = "修改成功",
                        Result = supplier.Id
                    };
                }
                else
                {
                    return new ApiResult<int>()
                    {
                        Code = ApiEnums.Error,
                        Message = "修改失败",
                        Result = 0
                    };
                }
            }
            catch (Exception ex)
            {
                return new ApiResult<int>()
                {
                    Code = ApiEnums.Error,
                    Message = $"修改失败：{ex.Message}",
                    Result = 0
                };
            }
        }


        /// <summary>
        /// 获取所有商品列表（去重）
        /// </summary>
        /// <returns></returns>
        public ApiResult<List<ProductModel>> GetAllProducts()
        {
            try
            {
                var sql = @"
                    SELECT DISTINCT
                        id, 
                        name, 
                        specification, 
                        unit, 
                        category, 
                        status, 
                        create_time
                    FROM DB_product 
                    WHERE status = 1
                    ORDER BY create_time DESC";

                var products = _repository.GetList(sql);

                if (products == null || !products.Any())
                {
                    return new ApiResult<List<ProductModel>>()
                    {
                        Code = ApiEnums.Error,
                        Message = "没有商品",
                        Result = new List<ProductModel>()
                    };
                }

                return new ApiResult<List<ProductModel>>()
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    Result = products
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<List<ProductModel>>()
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    Result = new List<ProductModel>()
                };
            }
        }

        /// <summary>
        /// 获取所有供应商列表
        /// </summary>
        /// <returns>供应商信息</returns>
        public ApiResult<List<SupplierModels>> GetSupplierById()
        {
            try
            {
                var sql = @"
                    SELECT 
                        id, 
                        name, 
                        contact_person, 
                        contact_phone, 
                        address, 
                        status, 
                        create_time
                    FROM DB_supplier 
                    ORDER BY create_time DESC";

                var suppliers = _supplierRepository.GetList(sql);

                if (suppliers == null || !suppliers.Any())
                {
                    return new ApiResult<List<SupplierModels>>()
                    {
                        Code = ApiEnums.Error,
                        Message = "没有供应商",
                        Result = new List<SupplierModels>()
                    };
                }

                return new ApiResult<List<SupplierModels>>()
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    Result = suppliers
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<List<SupplierModels>>()
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    Result = new List<SupplierModels>()
                };
            }
        }

        /// <summary>
        /// 为供应商分配商品
        /// </summary>
        /// <param name="assignmentDto">分配信息</param>
        /// <returns>操作结果</returns>
        public ApiResult<int> AssignProductToSupplier(SupplierProductAssignmentDto assignmentDto)
        {
            try
            {
                // 检查是否已经分配过
                var checkSql = @"
                    SELECT id FROM DB_supplier_product 
                    WHERE supplier_id = @SupplierId AND product_id = @ProductId";

                var existingAssignment = _dbContext.GetConn().QueryFirstOrDefault<int?>(checkSql, new
                {
                    assignmentDto.SupplierId,
                    assignmentDto.ProductId
                });

                if (existingAssignment.HasValue)
                {
                    return new ApiResult<int>()
                    {
                        Code = ApiEnums.Error,
                        Message = "该商品已分配给此供应商",
                        Result = 0
                    };
                }

                // 插入分配记录
                var insertSql = @"
                    INSERT INTO DB_supplier_product (
                        supplier_id, product_id, supply_price, is_default, create_time
                    ) VALUES (
                        @SupplierId, @ProductId, @SupplyPrice, @IsDefault, @CreateTime
                    );
                    SELECT LAST_INSERT_ID()";

                var parameters = new
                {
                    assignmentDto.SupplierId,
                    assignmentDto.ProductId,
                    assignmentDto.SupplyPrice,
                    assignmentDto.IsDefault,
                    CreateTime = DateTime.Now
                };

                var result = _dbContext.GetConn().ExecuteScalar<int>(insertSql, parameters);

                return new ApiResult<int>()
                {
                    Code = ApiEnums.Success,
                    Message = "分配成功",
                    Result = result
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<int>()
                {
                    Code = ApiEnums.Error,
                    Message = $"分配失败：{ex.Message}",
                    Result = 0
                };
            }
        }

        /// <summary>
        /// 移除供应商商品分配
        /// </summary>
        /// <param name="id">分配记录ID</param>
        /// <returns>操作结果</returns>
        public ApiResult<bool> RemoveProductFromSupplier(int id)
        {
            try
            {
                var sql = @"
                    DELETE FROM DB_supplier_product 
                    WHERE id = @Id";

                var result = _dbContext.GetConn().Execute(sql, new { Id = id });

                if (result > 0)
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Success,
                        Message = "移除成功",
                        Result = true
                    };
                }
                else
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Error,
                        Message = "移除失败，记录不存在",
                        Result = false
                    };
                }
            }
            catch (Exception ex)
            {
                return new ApiResult<bool>()
                {
                    Code = ApiEnums.Error,
                    Message = $"移除失败：{ex.Message}",
                    Result = false
                };
            }
        }

        /// <summary>
        /// 获取供应商已分配的商品列表
        /// </summary>
        /// <param name="queryDto">查询条件</param>
        /// <returns>分页结果</returns>
        public ApiPage<SupplierProductInfoDto> GetSupplierProducts(SupplierProductQueryDto queryDto)
        {
            try
            {
                var offset = (queryDto.PageIndex - 1) * queryDto.PageSize;

                var dataSql = @"
                    SELECT 
                        sp.id AS Id,
                        sp.supplier_id AS SupplierId,
                        s.name AS SupplierName,
                        sp.product_id AS ProductId,
                        p.name AS ProductName,
                        p.specification AS Specification,
                        p.unit AS Unit,
                        sp.supply_price AS SupplyPrice,
                        sp.is_default AS IsDefault,
                        sp.create_time AS CreateTime
                    FROM DB_supplier_product sp
                    INNER JOIN DB_supplier s ON sp.supplier_id = s.id
                    INNER JOIN DB_product p ON sp.product_id = p.id
                    WHERE sp.supplier_id = @SupplierId
                    ORDER BY sp.create_time DESC
                    LIMIT @PageSize OFFSET @Offset";

                var countSql = @"
                    SELECT COUNT(*) 
                    FROM DB_supplier_product sp
                    WHERE sp.supplier_id = @SupplierId";

                var parameters = new
                {
                    queryDto.SupplierId,
                    PageSize = queryDto.PageSize,
                    Offset = offset
                };

                var supplierProducts = _dbContext.GetConn().Query<SupplierProductInfoDto>(dataSql, parameters).ToList();
                var totalCount = _dbContext.GetConn().ExecuteScalar<int>(countSql, new { queryDto.SupplierId });

                return new ApiPage<SupplierProductInfoDto>()
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    TodalCount = totalCount,
                    PageData = supplierProducts
                };
            }
            catch (Exception ex)
            {
                return new ApiPage<SupplierProductInfoDto>()
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    TodalCount = 0,
                    PageData = new List<SupplierProductInfoDto>()
                };
            }
        }

        /// <summary>
        /// 获取可分配给供应商的商品列表（未分配的商品）
        /// </summary>
        /// <param name="supplierId">供应商ID</param>
        /// <returns>可分配商品列表</returns>
        public ApiResult<List<AvailableProductDto>> GetAvailableProducts(int supplierId)
        {
            try
            {
                var sql = @"
                    SELECT 
                        p.id,
                        p.name,
                        p.specification,
                        p.unit,
                        p.category,
                        p.status,
                        0 AS IsAssigned
                    FROM DB_product p
                    WHERE p.status = 1 
                    AND p.id NOT IN (
                        SELECT product_id 
                        FROM DB_supplier_product 
                        WHERE supplier_id = @SupplierId
                    )
                    ORDER BY p.create_time DESC";

                var availableProducts = _dbContext.GetConn().Query<AvailableProductDto>(sql, new { SupplierId = supplierId }).ToList();

                return new ApiResult<List<AvailableProductDto>>()
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    Result = availableProducts
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<List<AvailableProductDto>>()
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    Result = new List<AvailableProductDto>()
                };
            }
        }

        /// <summary>
        /// 更新供应商商品分配信息
        /// </summary>
        /// <param name="assignmentDto">分配信息</param>
        /// <returns>操作结果</returns>
        public ApiResult<bool> UpdateSupplierProduct(SupplierProductAssignmentDto assignmentDto)
        {
            try
            {
                // 检查分配记录是否存在
                var checkSql = @"
                    SELECT id FROM DB_supplier_product 
                    WHERE supplier_id = @SupplierId AND product_id = @ProductId";

                var existingId = _dbContext.GetConn().QueryFirstOrDefault<int?>(checkSql, new
                {
                    assignmentDto.SupplierId,
                    assignmentDto.ProductId
                });

                if (!existingId.HasValue)
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Error,
                        Message = "分配记录不存在",
                        Result = false
                    };
                }

                // 更新分配信息
                var updateSql = @"
                    UPDATE DB_supplier_product SET 
                        supply_price = @SupplyPrice,
                        is_default = @IsDefault
                    WHERE supplier_id = @SupplierId AND product_id = @ProductId";

                var parameters = new
                {
                    assignmentDto.SupplierId,
                    assignmentDto.ProductId,
                    assignmentDto.SupplyPrice,
                    assignmentDto.IsDefault
                };

                var result = _dbContext.GetConn().Execute(updateSql, parameters);

                if (result > 0)
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Success,
                        Message = "更新成功",
                        Result = true
                    };
                }
                else
                {
                    return new ApiResult<bool>()
                    {
                        Code = ApiEnums.Error,
                        Message = "更新失败",
                        Result = false
                    };
                }
            }
            catch (Exception ex)
            {
                return new ApiResult<bool>()
                {
                    Code = ApiEnums.Error,
                    Message = $"更新失败：{ex.Message}",
                    Result = false
                };
            }
        }

        /// <summary>
        /// 根据供应商ID获取可采购商品列表
        /// </summary>
        public ApiResult<List<SupplierProductInfoDto>> GetPurchaseProductsBySupplier(int supplierId)
        {
            try
            {
                var sql = @"
                    SELECT 
                        sp.id AS Id,
                        sp.supplier_id AS SupplierId,
                        s.name AS SupplierName,
                        sp.product_id AS ProductId,
                        p.name AS ProductName,
                        p.specification AS Specification,
                        p.unit AS Unit,
                        p.status AS Status,
                        sp.supply_price AS SupplyPrice,
                        sp.is_default AS IsDefault,
                        sp.create_time AS CreateTime
                    FROM DB_supplier_product sp
                    INNER JOIN DB_supplier s ON sp.supplier_id = s.id
                    INNER JOIN DB_product p ON sp.product_id = p.id
                    WHERE sp.supplier_id = @SupplierId
                ";
                var products = _dbContext.GetConn().Query<SupplierProductInfoDto>(sql, new { SupplierId = supplierId }).ToList();
                return new ApiResult<List<SupplierProductInfoDto>>
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    Result = products
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<List<SupplierProductInfoDto>>
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    Result = new List<SupplierProductInfoDto>()
                };
            }
        }

        /// <summary>
        /// 创建采购单
        /// </summary>
        public ApiResult<int> CreatePurchaseOrder(DbPurchaseList purchase)
        {
            try
            {
                var sql = @"
                    INSERT INTO DB_purchase_list
                    (order_no, purchase_date, supplier_id, purchaser, products_info, total_amount, status, remark)
                    VALUES
                    (@OrderNo, @PurchaseDate, @SupplierId, @Purchaser, @ProductsInfo, @TotalAmount, @Status, @Remark);
                    SELECT LAST_INSERT_ID();
                ";
                var id = _dbContext.GetConn().ExecuteScalar<int>(sql, new
                {
                    purchase.OrderNo,
                    purchase.PurchaseDate,
                    purchase.SupplierId,
                    purchase.Purchaser,
                    purchase.ProductsInfo,
                    purchase.TotalAmount,
                    purchase.Status,
                    purchase.Remark
                });
                return new ApiResult<int>
                {
                    Code = ApiEnums.Success,
                    Message = "采购单创建成功",
                    Result = id
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<int>
                {
                    Code = ApiEnums.Error,
                    Message = $"采购单创建失败：{ex.Message}",
                    Result = 0
                };
            }
        }

        /// <summary>
        /// 分页查询采购单
        /// </summary>
        public ApiPage<DbPurchaseList> GetPurchaseOrderPage(PurchaseOrderQueryDto query)
        {
            try
            {
                var conditions = new List<string>();
                var parameters = new DynamicParameters();

                if (query.SupplierId.HasValue)
                {
                    conditions.Add("supplier_id = @SupplierId");
                    parameters.Add("@SupplierId", query.SupplierId);
                }
                if (!string.IsNullOrEmpty(query.OrderNo))
                {
                    conditions.Add("order_no LIKE @OrderNo");
                    parameters.Add("@OrderNo", $"%{query.OrderNo}%");
                }
                if (query.StartDate.HasValue)
                {
                    conditions.Add("purchase_date >= @StartDate");
                    parameters.Add("@StartDate", query.StartDate.Value.Date);
                }
                if (query.EndDate.HasValue)
                {
                    conditions.Add("purchase_date <= @EndDate");
                    parameters.Add("@EndDate", query.EndDate.Value.Date);
                }
                var whereClause = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
                var offset = (query.PageIndex - 1) * query.PageSize;
                var sql = $@"
                    SELECT 
                        id AS Id,
                        order_no AS OrderNo,
                        purchase_date AS PurchaseDate,
                        supplier_id AS SupplierId,
                        purchaser AS Purchaser,
                        products_info AS ProductsInfo,
                        total_amount AS TotalAmount,
                        status AS Status,
                        remark AS Remark
                    FROM DB_purchase_list
                    {whereClause}
                    ORDER BY purchase_date DESC, id DESC
                    LIMIT @PageSize OFFSET @Offset
                ";
                var countSql = $@"
                    SELECT COUNT(*) FROM DB_purchase_list {whereClause}
                ";
                parameters.Add("@PageSize", query.PageSize);
                parameters.Add("@Offset", offset);
                var list = _dbContext.GetConn().Query<DbPurchaseList>(sql, parameters).ToList();
                var total = _dbContext.GetConn().ExecuteScalar<int>(countSql, parameters);
                return new ApiPage<DbPurchaseList>
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    TodalCount = total,
                    PageData = list
                };
            }
            catch (Exception ex)
            {
                return new ApiPage<DbPurchaseList>
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    TodalCount = 0,
                    PageData = new List<DbPurchaseList>()
                };
            }
        }

        /// <summary>
        /// 查询进货列表（支持多种筛选条件）
        /// </summary>
        public ApiPage<PurchaseListDto> GetPurchaseList(PurchaseListQueryDto query)
        {
            try
            {
                var conditions = new List<string>();
                var parameters = new DynamicParameters();

                // 构建查询条件
                if (query.SupplierId.HasValue)
                {
                    conditions.Add("pl.supplier_id = @SupplierId");
                    parameters.Add("@SupplierId", query.SupplierId.Value);
                }

                if (!string.IsNullOrEmpty(query.OrderNo))
                {
                    conditions.Add("pl.order_no LIKE @OrderNo");
                    parameters.Add("@OrderNo", $"%{query.OrderNo}%");
                }

                if (!string.IsNullOrEmpty(query.Purchaser))
                {
                    conditions.Add("pl.purchaser LIKE @Purchaser");
                    parameters.Add("@Purchaser", $"%{query.Purchaser}%");
                }

                if (!string.IsNullOrEmpty(query.Status))
                {
                    conditions.Add("pl.status = @Status");
                    parameters.Add("@Status", query.Status);
                }

                if (query.StartDate.HasValue)
                {
                    conditions.Add("pl.purchase_date >= @StartDate");
                    parameters.Add("@StartDate", query.StartDate.Value.Date);
                }

                if (query.EndDate.HasValue)
                {
                    conditions.Add("pl.purchase_date <= @EndDate");
                    parameters.Add("@EndDate", query.EndDate.Value.Date);
                }

                var whereClause = conditions.Count > 0 ? "WHERE " + string.Join(" AND ", conditions) : "";
                var offset = (query.PageIndex - 1) * query.PageSize;

                // 主查询：获取进货列表数据
                var sql = $@"
                    SELECT 
                        pl.id AS Id,
                        pl.order_no AS OrderNo,
                        pl.purchase_date AS PurchaseDate,
                        pl.supplier_id AS SupplierId,
                        s.name AS SupplierName,
                        pl.purchaser AS Purchaser,
                        pl.products_info AS ProductsInfo,
                        JSON_LENGTH(pl.products_info) AS ProductCount,
                        pl.total_amount AS TotalAmount,
                        pl.status AS Status,
                        pl.remark AS Remark
                    FROM DB_purchase_list pl
                    LEFT JOIN DB_supplier s ON pl.supplier_id = s.id
                    {whereClause}
                    ORDER BY pl.purchase_date DESC, pl.id DESC
                    LIMIT @PageSize OFFSET @Offset
                ";

                // 总记录数查询
                var countSql = $@"
                    SELECT COUNT(*) 
                    FROM DB_purchase_list pl
                    LEFT JOIN DB_supplier s ON pl.supplier_id = s.id
                    {whereClause}
                ";

                parameters.Add("@PageSize", query.PageSize);
                parameters.Add("@Offset", offset);

                var list = _dbContext.GetConn().Query<PurchaseListDto>(sql, parameters).ToList();
                var total = _dbContext.GetConn().ExecuteScalar<int>(countSql, parameters);

                return new ApiPage<PurchaseListDto>
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    TodalCount = total,
                    PageData = list
                };
            }
            catch (Exception ex)
            {
                return new ApiPage<PurchaseListDto>
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    TodalCount = 0,
                    PageData = new List<PurchaseListDto>()
                };
            }
        }

        /// <summary>
        /// 获取供应商下拉框数据
        /// </summary>
        public ApiResult<List<SupplierDropdownDto>> GetSupplierDropdown()
        {
            try
            {
                var sql = @"
                    SELECT 
                        id AS Id,
                        name AS Name
                    FROM DB_supplier
                    WHERE status = 1
                    ORDER BY name ASC
                ";
                var suppliers = _dbContext.GetConn().Query<SupplierDropdownDto>(sql).ToList();
                return new ApiResult<List<SupplierDropdownDto>>
                {
                    Code = ApiEnums.Success,
                    Message = "查询成功",
                    Result = suppliers
                };
            }
            catch (Exception ex)
            {
                return new ApiResult<List<SupplierDropdownDto>>
                {
                    Code = ApiEnums.Error,
                    Message = $"查询失败：{ex.Message}",
                    Result = new List<SupplierDropdownDto>()
                };
            }
        }
    }
}
